package com.wolfpire.system.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Repository;

import com.wolfpire.system.common.Constants;
import com.wolfpire.system.common.Page;
import com.wolfpire.system.common.base.dao.impl.BaseHibernateDao;
import com.wolfpire.system.dao.UserDao;
import com.wolfpire.system.model.Subject;
import com.wolfpire.system.model.User;
import com.wolfpire.system.model.UserGroupSubjectRelation;
import com.wolfpire.system.model.UserGroupUserRelation;

@Repository("userDao")
public class UserDaoImpl extends BaseHibernateDao<User, Long> implements UserDao {

	
	public User getByAccount(String account, String password) {
		Criterion[] criterions = new Criterion[]{
				Restrictions.eq("account", account),
				Restrictions.eq("password", password),
				Restrictions.eq("delFlag", Constants.NORMAL_FLAG)
		};
		return findUniqueByCriteria(criterions);
	}

	@SuppressWarnings("unchecked")
	
	public List<User> list(User user) {
		Criteria c = createCriteria(createFilter(user));
		return c.list();
	}
	
	private Criterion[] createFilter(User user) {
		if (null == user) {
			return null;
		}
		List<Criterion> criterions = new ArrayList<Criterion>();
		if (null != user.getId()) {
			criterions.add(Restrictions.eq("id", user.getId()));
		}
		if (!StringUtils.isBlank(user.getAccount())) {
			criterions.add(Restrictions.like("account", user.getAccount(), MatchMode.ANYWHERE));
		}
		if (!StringUtils.isBlank(user.getPassword())) {
			criterions.add(Restrictions.eq("password", user.getPassword()));
		}
		if (!StringUtils.isBlank(user.getNickName())) {
			criterions.add(Restrictions.like("nickName", user.getNickName(), MatchMode.ANYWHERE));
		}
		if (null != user.getStatus()) {
			criterions.add(Restrictions.ge("status", user.getStatus()));
		}
		if (null != user.getDelFlag()) {
			criterions.add(Restrictions.eq("delFlag", user.getDelFlag()));
		}
		if (null != user.getAge()) {
			criterions.add(Restrictions.ge("age", user.getAge()));
		}
		if (null != user.getGender()) {
			criterions.add(Restrictions.ge("gender", user.getGender()));
		}
		
		return criterions.toArray(new Criterion[criterions.size()]);
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	
	public Page<User> findUsers(Page<User> page, User filterUser) {
		/*
		Criterion[] criterions = createFilter(filterUser);
		int totalCount = this.findIntByCriteria(criterions);
		page.setTotalCount(totalCount);
		
		if (0 < totalCount) {
			Criteria c = createCriteria(criterions);
			c = this.setPageParameter(c, page);
			@SuppressWarnings({ "unchecked" })
			List<User> result = c.list();
			page.setDataList(result);
		}
		return page;*/
		StringBuffer querySqlBuffer = new StringBuffer();
		StringBuffer countSqlBuffer = new StringBuffer();
		List<String> propertyNames = new ArrayList<String>();
		List propertyValues = new ArrayList();
		//querySqlBuffer.append(" SELECT u.* FROM t_sm_user u, t_sm_org o, t_sm_user_org uo ")
		querySqlBuffer.append(" SELECT u.* FROM t_sm_user u ")
				.append(" LEFT JOIN t_sm_user_org uo ON u.id = uo.user_id ")
				.append(" LEFT JOIN t_sm_org o ON o.id = uo.org_id ")
				.append(" WHERE u.del_flag = 1 AND IFNULL(o.del_flag, 1) = 1 ");
				//.append(" WHERE u.id = uo.user_id AND uo.org_id = o.id ");
		if (null != filterUser.getId()) {
			querySqlBuffer.append(" AND u.id =:id");
			propertyNames.add("id");
			propertyValues.add(filterUser.getId());
		}
		if (!StringUtils.isBlank(StringUtils.stripToEmpty(filterUser.getAccount()))) {
			querySqlBuffer.append(" AND u.account LIKE :account ");
			propertyNames.add("account");
			propertyValues.add("%" + StringUtils.stripToEmpty(filterUser.getAccount()) + "%");
		}
		if (!StringUtils.isBlank(StringUtils.stripToEmpty(filterUser.getNickName()))) {
			querySqlBuffer.append(" AND u.nickname LIKE :nickname ");
			propertyNames.add("nickname");
			propertyValues.add("%" + StringUtils.stripToEmpty(filterUser.getNickName()) + "%");
		}
		if (!StringUtils.isBlank(StringUtils.stripToEmpty(filterUser.getEmail()))) {
			querySqlBuffer.append(" AND u.email LIKE :email ");
			propertyNames.add("email");
			propertyValues.add("%" + StringUtils.stripToEmpty(filterUser.getEmail()) + "%");
		}
		if (null != filterUser.getStatus()) {
			querySqlBuffer.append(" AND u.status = :status ");
			propertyNames.add("status");
			propertyValues.add(filterUser.getStatus());
		}
		if (null != filterUser.getOrgId()) {
			querySqlBuffer.append(" AND o.id= :orgId ");
			propertyNames.add("orgId");
			propertyValues.add(filterUser.getOrgId());
		}
		countSqlBuffer.append(" SELECT count(1) from (").append(querySqlBuffer).append(") AS TOTAL ");
		int totalCount = this.findSqlInt(countSqlBuffer.toString(), propertyNames, propertyValues);
		page.setTotalCount(totalCount);
		querySqlBuffer.append(" ORDER BY u.id DESC ");
		if (0 < totalCount) {
			List<User> result = this.setPageParameter(page, User.class, querySqlBuffer.toString(), propertyNames, propertyValues);
			page.setDataList(result);
		}
		
		return page;
	}

	
	public User getByAccount(String account) {
		Criterion[] criterions = new Criterion[]{
				Restrictions.eq("account", account),
				Restrictions.eq("delFlag", Constants.NORMAL_FLAG)
		};
		return findUniqueByCriteria(criterions);
	}

	
	public void saveUserPassword(Long id, String password) {
		StringBuffer executeSql = new StringBuffer();
		executeSql.append(" UPDATE t_sm_user SET password = ? WHERE id = ?");
		Object[] values = new Object[]{password, id};
		this.executeSql(executeSql.toString(), values);
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	
	public List<User> getUsersByParam(User user, List<Long> roleIds,
			List<Long> orgIds) {
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT * FROM t_sm_user u, t_sm_user_role ur, t_sm_role r ")
					.append(" WHERE u.id = ur.user_id AND ur.role_id = r.id ")
					.append(" AND r.id IN (:roleIds) ");
		List<String> propertyNames = new ArrayList<String>();
		propertyNames.add("roleIds");
		
		List propertyValues = new ArrayList();
		propertyValues.add(roleIds);
		if (!StringUtils.isBlank(user.getNickName())) {
			querySql.append("  AND u.nickname LIKE :nickName ");
			propertyNames.add("nickName");
			propertyValues.add("%" + user.getNickName() + "%");
		}
		querySql.append(" AND u.del_flag=1 AND r.del_flag=1");
		SQLQuery query = this.createSqlQuery(querySql.toString(), propertyNames, propertyValues);
		query.addEntity(User.class);
		return query.list();
	}
	/**
	 * 通过用户手机号码查找用户
	 */
	public User getByTelephone(String telephone) {
		Criterion[] criterions = new Criterion[]{
				Restrictions.eq("telephoneId", telephone),
				Restrictions.eq("delFlag", Constants.NORMAL_FLAG)
		};
		return findUniqueByCriteria(criterions);
	}

	public User getByTelephone(String telephone, String password) {
		Criterion[] criterions = new Criterion[]{
				Restrictions.eq("telephoneId", telephone),
				Restrictions.eq("password", password),
				Restrictions.eq("delFlag", Constants.NORMAL_FLAG)
		};
		return findUniqueByCriteria(criterions);
	}

	public void registUser(String account, String password) {
		// TODO Auto-generated method stub
		
		
	}

	public Page<Map<String, Object>> findUsers(Page<Map<String, Object>> page, User filterUser, long usergroupId) {
		StringBuffer querySqlBuffer = new StringBuffer();
		StringBuffer countSqlBuffer = new StringBuffer();
		List<String> propertyNames = new ArrayList<String>();
		List propertyValues = new ArrayList();
		//querySqlBuffer.append(" SELECT u.* FROM t_sm_user u, t_sm_org o, t_sm_user_org uo ")
		querySqlBuffer.append(" SELECT u.* FROM t_sm_user u ")
				.append(" LEFT JOIN t_sm_user_org uo ON u.id = uo.user_id ")
				.append(" LEFT JOIN t_sm_org o ON o.id = uo.org_id ")
				.append(" WHERE u.del_flag = 1 AND IFNULL(o.del_flag, 1) = 1 ");
				//.append(" WHERE u.id = uo.user_id AND uo.org_id = o.id ");
		if (null != filterUser.getId()) {
			querySqlBuffer.append(" AND u.id =:id");
			propertyNames.add("id");
			propertyValues.add(filterUser.getId());
		}
		if (!StringUtils.isBlank(StringUtils.stripToEmpty(filterUser.getAccount()))) {
			querySqlBuffer.append(" AND u.account LIKE :account ");
			propertyNames.add("account");
			propertyValues.add("%" + StringUtils.stripToEmpty(filterUser.getAccount()) + "%");
		}
		if (!StringUtils.isBlank(StringUtils.stripToEmpty(filterUser.getNickName()))) {
			querySqlBuffer.append(" AND u.nickname LIKE :nickname ");
			propertyNames.add("nickname");
			propertyValues.add("%" + StringUtils.stripToEmpty(filterUser.getNickName()) + "%");
		}
		if (!StringUtils.isBlank(StringUtils.stripToEmpty(filterUser.getEmail()))) {
			querySqlBuffer.append(" AND u.email LIKE :email ");
			propertyNames.add("email");
			propertyValues.add("%" + StringUtils.stripToEmpty(filterUser.getEmail()) + "%");
		}
		if (null != filterUser.getStatus()) {
			querySqlBuffer.append(" AND u.status = :status ");
			propertyNames.add("status");
			propertyValues.add(filterUser.getStatus());
		}
		if (null != filterUser.getOrgId()) {
			querySqlBuffer.append(" AND o.id= :orgId ");
			propertyNames.add("orgId");
			propertyValues.add(filterUser.getOrgId());
		}
		countSqlBuffer.append(" SELECT count(1) from (").append(querySqlBuffer).append(") AS TOTAL ");
		int totalCount = this.findSqlInt(countSqlBuffer.toString(), propertyNames, propertyValues);
		page.setTotalCount(totalCount);
		querySqlBuffer.append(" ORDER BY u.id DESC ");
		SQLQuery query = this.createSqlQuery(querySqlBuffer.toString(), propertyNames, propertyValues);
		query.addEntity(User.class);
		List<User> users = query.list();
		
		//用户组和课程的关联表,查询出对象组
		StringBuffer querySql = new StringBuffer();
		List<String> propertyNames1 = new ArrayList<String>();
		List propertyValues1 = new ArrayList();
		querySql.append(" select ugu.* from t_usergroup_user ugu where ugu.usergroup_id = :usergroupId");
		propertyNames1.add("usergroupId");
		propertyValues1.add(usergroupId);
		SQLQuery query1 = this.createSqlQuery(querySql.toString(), propertyNames1, propertyValues1);
		query1.addEntity(UserGroupUserRelation.class);
		List<UserGroupUserRelation> lists1 = query1.list();
		List<Long> s = new ArrayList<Long>();
		for(UserGroupUserRelation userGroupUserRelation : lists1)
			s.add(userGroupUserRelation.getUserId());                      //用户组中的课程
		List<Map<String, Object>> lists = new ArrayList<Map<String,Object>>();
		for(User user : users){
			Map<String, Object> userMap = new HashMap<String, Object>();
			if(s.contains(user.getId()))                                     //被勾选过的课程
				userMap.put("ck", true);
			else
				userMap.put("ck", false);                                    //未勾选的课程
			userMap.put("id", user.getId());
			userMap.put("nickName", user.getNickName());
			userMap.put("account", user.getAccount());
			userMap.put("email", user.getEmail());
			userMap.put("createTime", user.getCreateTime());
			userMap.put("telephoneId", user.getTelephoneId());
			userMap.put("remark", user.getRemark());
			userMap.put("status", user.getStatus());
			lists.add(userMap);
		}
		if (0 < totalCount) {
			page.setDataList(lists);
		}
		/*if (0 < totalCount) {
			List<User> result = this.setPageParameter(page, User.class, querySqlBuffer.toString(), propertyNames, propertyValues);
			page.setDataList(result);
		}*/
		
		return page;
	}
}
